import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.LinkedList;
import java.io.*;

public class jdbc {
	
	public static final String DATE_FORMAT = "EEE, d MMM yyyy HH:mm:ss";
	public static SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);

	public static Connection openDBConnection(String user, String pass,
			String SID, String host, int port) throws SQLException,
			ClassNotFoundException {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + SID;
		String username = user;
		String password = pass;
		Class.forName(driver);
		return DriverManager.getConnection(url, username, password);
	}
	
	public static int getIntFromDB(Connection conn, String query) throws SQLException{
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		int ret = Integer.MIN_VALUE;
		if(rs.next()){
			ret = rs.getInt(1);
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static void executeUpdate(Connection conn, String query) throws SQLException{
		Statement st = conn.createStatement();
		st.executeUpdate(query);
		st.close();
	}
	
	public static String getDate(){
		Calendar cal = Calendar.getInstance();
		return sdf.format(cal.getTime());
	}
	
	public static void resetDB(Connection conn) throws SQLException {
		Statement st = conn.createStatement();
		try {
			BufferedReader in = new BufferedReader(new FileReader("CIS330_Twitter.sql"));
			String str;
			StringBuffer sb = new StringBuffer();
			while ((str = in.readLine()) != null) {
				if (str.endsWith(";")) {
					str = str.substring(0, str.length() - 1);
					sb.append(str);
					st.executeQuery(sb.toString());
					sb = new StringBuffer();
					continue;
				}
				sb.append(str + "\n");
			}
			in.close();
			
		} catch (Exception e) {
			System.err.println("Failed to Execute. The error is "+ e.getMessage());
		}
	} 
	
	public static void newUser(Connection conn, String[] args) throws SQLException {
		
		Statement st = conn.createStatement();
		String qargs = "";
		for (int i = 0; i < args.length; i++) {
			qargs += "'";
			qargs += args[i];
			qargs += "'";
			if (i == args.length - 1)
				break;
			qargs += ", ";
		}
		st.executeQuery("insert into Users values (" + qargs + ")");
		st.close();
	}
	
	public static ResultSet getUser(Connection conn, String user) throws SQLException {
		Statement st = conn.createStatement();
		String qargs = "select * from Users where name = '" + user +"'";
		ResultSet rs = st.executeQuery(qargs);
		rs.next();
		rs.getString("name");
		return rs;
	}
	
	public static String getUserEmail(Connection conn, String email) throws SQLException {
		Statement st = conn.createStatement();
		String qargs = "select * from Users where email = '" + email +"'";
		ResultSet rs = st.executeQuery(qargs);
		rs.next();
		return rs.getString(1);
	}
	
	public static void updateUser(Connection conn, String user, String[] args) throws SQLException {
		// Set each field in user to be the newly entered data
		String[] fields = {"pass", "privacyPolicy", "firstName", "lastName", "language", "timezone", "bio", "location", "webpage"};
		Statement st = conn.createStatement();
		String qargs = "update Users set ";
		for(int i = 0; i < args.length; i++){
			qargs += fields[i] +  " = '" + args[i] + "'";
			if (i == args.length - 1)
				break;
			qargs += ", ";
		}
		qargs += " where name = '" + user + "'";
		st.executeQuery(qargs);
	}
	
	public static void newTweet(Connection conn, String user, String tweet) throws SQLException {
		// ToDo
		String query, temp;
		
		// get MaxID from database, increment by 1
		// Update MaxID table ???????????
		//Get max msgID and determine msgID of this tweet
		int newMID = getIntFromDB(conn, "select max(msgID) from Msgs") + 1;

		// Add to tweet and msg table
		tweet = tweet.replace("'", "''");

		// insert into Msgs  values (maxID, date, tweet);
		query = "insert into Msgs (msgID, msgDate, content) values (" + newMID + ", TO_DATE('" + getDate() + "', 'DY, DD MON YYYY HH24:MI:SS'), '" + tweet + "')";

		executeUpdate(conn, query);
		// insert into Tweets values (maxID);
		query = "insert into Tweets (msgID) values (" + newMID +")";
		executeUpdate(conn, query);
		// insert into Writes values (user, maxID);
		query = "insert into Writes (author, msgID) values ('" + user + "', " + newMID + ")";
		executeUpdate(conn, query);
		
		// Search for #hashtag and add to hashtable
		String[] ht = tweet.split("#");
		LinkedList<String> hashTags = new LinkedList<String>();
		for (int i = 0; i < ht.length; i ++){
			if(i%2 == 1){
				temp = (ht[i].split(" "))[0];
				hashTags.add(temp);
				//System.out.println("adding hash tag: " + temp);
				query = "insert into ContainsHashTags (tag, msgID) values ('" + temp + "', " + newMID + ")";
				executeUpdate(conn, query);
			}
		}
		// Search for @user and add to mentions
		String[] m = tweet.split("@");
		LinkedList<String> mentions = new LinkedList<String>();
		for (int i = 0; i < m.length; i ++){
			if(i%2 == 1){
				temp = (m[i].split(" "))[0];
				mentions.add(temp);
				//System.out.println("adding mention: " + temp);
				query = "insert into Mentions (name, msgID) values ('" + temp + "', " + newMID + ")";
				executeUpdate(conn, query);
			}
		}
	}
	
	public static void remTweet(Connection conn, int msgid) throws SQLException {
		
		String query;
		// ToDo

		// delete from Tweets where msgID = 'msgid';
		query = "delete from Tweets where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from Writes where msgID = 'msgid';
		query = "delete from Writes where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from Mentions where msgID = 'msgid';
		query = "delete from Mentions where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from ContainsHashTag where msgID = 'msgid';
		query = "delete from ContainsHashTags where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from Msgs where msgID = 'msgid';
		query = "delete from Msgs where msgID = " + msgid + "";
		executeUpdate(conn, query);
	}
	
	public static void newMsg(Connection conn, String user, String tweet, String userto) throws SQLException {
		
		String query, temp;
		
		// get MaxID from database, increment by 1
		// Update MaxID table ???????????
		//Get max msgID and determine msgID of this tweet
		int newMID = getIntFromDB(conn, "select max(msgID) from Msgs") + 1;

		// Add to tweet and msg table
		tweet = tweet.replace("'", "''");

		// insert into Msgs  values (maxID, date, tweet);
		query = "insert into Msgs (msgID, msgDate, content) values (" + newMID + ", TO_DATE('" + getDate() + "', 'DY, DD MON YYYY HH24:MI:SS'), '" + tweet + "')";
		//System.out.println(query);
		executeUpdate(conn, query);
		// insert into PrivateMsgs values (maxID);
		query = "insert into PrivateMsgs (addressee, msgID) values ('" + userto + "', " + newMID +")";
		executeUpdate(conn, query);
		// insert into Writes values (user, maxID);
		query = "insert into Writes (author, msgID) values ('" + user + "', " + newMID + ")";
		executeUpdate(conn, query);
		
		// Search for #hashtag and add to hashtable
		String[] ht = tweet.split("#");
		LinkedList<String> hashTags = new LinkedList<String>();
		for (int i = 0; i < ht.length; i ++){
			if(i%2 == 1){
				temp = (ht[i].split(" "))[0];
				hashTags.add(temp);
				query = "insert into ContainsHashTags (tag, msgID) values ('" + temp + "', " + newMID + ")";
				executeUpdate(conn, query);
			}
		}
		// Search for @user and add to mentions
		String[] m = tweet.split("@");
		LinkedList<String> mentions = new LinkedList<String>();
		for (int i = 0; i < ht.length; i ++){
			if(i%2 == 1){
				temp = (m[i].split(" "))[0];
				mentions.add(temp);
				query = "insert into Mentions (name, msgID) values ('" + temp + "', " + newMID + ")";
				executeUpdate(conn, query);
			}
		}
	}
	
	public static void remMsg(Connection conn, int msgid) throws SQLException {
		// ToDo
		// delete from Msgs where msgID = 'msgid';
		// delete from PrivateMsgs where msgID = 'msgid';
		// delete from Writes where msgID = 'msgid';
		// delete from Mentions where msgID = 'msgid';
		// delete from ContainsHashTag where msgID = 'msgid';
		String query;
		// ToDo

		// delete from Tweets where msgID = 'msgid';
		query = "delete from Tweets where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from Writes where msgID = 'msgid';
		query = "delete from Writes where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from Mentions where msgID = 'msgid';
		query = "delete from Mentions where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from ContainsHashTag where msgID = 'msgid';
		query = "delete from ContainsHashTags where msgID = " + msgid + "";
		executeUpdate(conn, query);
		// delete from Msgs where msgID = 'msgid';
		query = "delete from Msgs where msgID = " + msgid + "";
		executeUpdate(conn, query);
	}
	
	public static void newFollowAccepted(Connection conn, String user, String userto) throws SQLException {
		
		String query;
		query = "insert into Follows (follower, followee) values ('" + user + "', '" + userto + "')";
		executeUpdate(conn, query);
		// 1/0 = select privacyPolicy from Users where name = 'userto';
		// If 0, insert into Follows values (user, userto);
		// If 1, insert into WaitingToFollow values (user, userto);
	}
	
	public static void newFollow(Connection conn, String user, String userto) throws SQLException {
		
		String query;
		
		// ToDo
		// Check privacy setting of user wanting to follow, if public add to Follows table
		query = "select privacyPolicy from Users where name='" + userto + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		String pp = rs.getString(1);
		st.close();
		rs.close();
		if(pp.equalsIgnoreCase("N")){
			query = "insert into Follows (follower, followee) values ('" + user + "', '" + userto + "')";
			executeUpdate(conn, query);
		}else{
			// If protected add to waiting to follow table
			query = "insert into WaitingToFollow (follower, followee) values ('" + user + "', '" + userto + "')";
			executeUpdate(conn, query);
		}
		// 1/0 = select privacyPolicy from Users where name = 'userto';
		// If 0, insert into Follows values (user, userto);
		// If 1, insert into WaitingToFollow values (user, userto);
	}
	
	public static void remFollow(Connection conn, String user, String userto) throws SQLException {
		// ToDo
		// delete from Follows where follower = 'userto' and followee = 'user';
		String query = "delete from Follows where follower='" + userto + "' AND followee='" + user + "'";
		executeUpdate(conn, query);
	}
	
	public static void remWTF(Connection conn, String user, String userto) throws SQLException {
		// ToDo
		// delete from WaitingToFollow where follower = 'userto' and followee = 'user';
		String query = "delete from WaitingToFollow where follower='" + user + "' AND followee='" + userto + "'";
		executeUpdate(conn, query);
	}
	
	public static void newList(Connection conn, String user, char privacy, String name, String desc) throws SQLException {
		// ToDo
		// Get MaxListID, increment by 1, and update MaxListID
		int newLID = getIntFromDB(conn, "select max(listID) from Lists") + 1;

		// Add to tweet and msg table
		
		String query; 
		query = "select count(name) from Lists where name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		int x = rs.getInt(1);
		if(x == 0){
		// insert into Lists values (privacy, MaxListID, user);
			query = "insert into Lists (isPrivate, listID, creator, name, description) values ('"
				+ privacy + "', " + newLID + ", '" + user + "', '" + name + "', '" + desc + "')";
			executeUpdate(conn, query);
		}
		
		followListAccepted(conn, user, newLID);
	}
	
	public static void remList(Connection conn, String name) throws SQLException {
		// ToDo
		String query = "Select listID From Lists where name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		int lid = rs.getInt("listID");
		st.close();
		rs.close();
		query = "delete from ListHasMembers where listID = " + lid + "";
		executeUpdate(conn, query);
		query = "delete from ListHasWaitingMembers where listID = " + lid + "";
		executeUpdate(conn, query);
		// delete from Lists where listID = 'lid';
		query = "delete from Lists where listID = " + lid + "";
		executeUpdate(conn, query);
	}
	
	public static void remList(Connection conn, int lid) throws SQLException {
		// ToDo
		String query = "delete from ListHasMembers where listID = " + lid + "";
		executeUpdate(conn, query);
		query = "delete from ListHasWaitingMembers where listID = " + lid + "";
		executeUpdate(conn, query);
		// delete from Lists where listID = 'lid';
		query = "delete from Lists where listID = " + lid + "";
		executeUpdate(conn, query);
	}
	
	public static void addMemberToList(Connection conn, String u, String user, String name) throws SQLException {
		// ToDo
		String query = "select isPrivate, listID from Lists where name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		String pp = rs.getString("isPrivate");
		int lid = rs.getInt("listID");
		st.close();
		rs.close();
		query = "select creator from Lists where name = '" + name + "'";
		st = conn.createStatement();
		rs = st.executeQuery(query);
		rs.next();
		String creator = rs.getString(1);
		st.close();
		rs.close();
		
		// If public follow list, else wait for approval from creator
		if(pp.equalsIgnoreCase("N") || creator.equals(u)){
			query = "insert into ListHasMembers (member, listID) values ('" + user + "', " + lid + ")";
			executeUpdate(conn, query);
		}else{
			query = "insert into ListHasWaitingMembers (member, listID) values ('" + user + "', " + lid + ")";
			executeUpdate(conn, query);
		}
		// 1/0 = select isPrivate from Lists where listID = 'lid';
		// If 0, insert into ListHasMembers values (user, lid);
		// If 1, insert into ListHasWaitingMembers values (user, lid);
	}
	
	public static void addMemberToList(Connection conn, String u, String user, int lid) throws SQLException {
		// ToDo
		String query = "select isPrivate from Lists where listID = " + lid + "";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		String pp = rs.getString("isPrivate");
		st.close();
		rs.close();
		query = "select creator from Lists where listID = " + lid + "";
		st = conn.createStatement();
		rs = st.executeQuery(query);
		rs.next();
		String creator = rs.getString(1);
		st.close();
		rs.close();
		// If public follow list, else wait for approval from creator
		if(pp.equalsIgnoreCase("N") || creator.equals(u)){
			query = "insert into ListHasMembers (member, listID) values ('" + user + "', " + lid + ")";
			executeUpdate(conn, query);
		}else{
			query = "insert into ListHasWaitingMembers (member, listID) values ('" + user + "', " + lid + ")";
			executeUpdate(conn, query);
		}
		// 1/0 = select isPrivate from Lists where listID = 'lid';
		// If 0, insert into ListHasMembers values (user, lid);
		// If 1, insert into ListHasWaitingMembers values (user, lid);
	}
	
	public static void addMemberToListAccepted(Connection conn, String user, int lid) throws SQLException {
		// TODO Auto-generated method stub
		String query = "insert into ListHasMembers (member, listID) values ('" + user + "', " + lid + ")";
		executeUpdate(conn, query);
		query = "delete from ListHasWaitingMembers where listID = " + lid + "";
		executeUpdate(conn, query);
	}
	
	public static void remMemberFromList(Connection conn, String user, int lid) throws SQLException {
		// ToDo
		// delete from ListHasMembers where member = 'user' and listID = 'lid';
		String query = "delete from ListHasMembers where member='" + user + "' AND listID = " + lid +"";
		executeUpdate(conn, query);
	}
	
	public static void remMemberFromList(Connection conn, String user, String name) throws SQLException {
		// ToDo
		// delete from ListHasMembers where member = 'user' and listID = 'lid';
		String query = "Select listID From Lists where name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		int lid = rs.getInt("listID");
		st.close();
		rs.close();
		query = "delete from ListHasMembers where member='" + user + "' AND listID = " + lid +"";
		executeUpdate(conn, query);
	}
	
	public static void followList(Connection conn, String user, String name) throws SQLException {
		// ToDo
		// insert into FollowsList values (lid, user);
		String query = "Select listID, isPrivate, creator From Lists where name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		int lid = rs.getInt("listID");
		String pp = rs.getString("isPrivate");
		String x = rs.getString("creator");
		st.close();
		rs.close();
		if((x.equals(user) || pp.equals("N"))){
			query = "insert into FollowsList (listID, userFL) values (" + lid + ", '" + user + "')";
			executeUpdate(conn, query);
		}else{
			query = "insert into WaitingToFollowList (listID, userFL) values (" + lid + ", '" + user + "')";
			executeUpdate(conn, query);
		}
	}

	public static void followList(Connection conn, String user, int lid) throws SQLException {
		// ToDo
		// insert into FollowsList values (lid, user);
		String query = "Select isPrivate, creator From Lists where listID = " + lid + "";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		String pp = rs.getString("isPrivate");
		String x = rs.getString("creator");
		st.close();
		rs.close();
		if((x.equals(user) || pp.equals("N"))){
			query = "insert into FollowsList (listID, userFL) values (" + lid + ", '" + user + "')";
			executeUpdate(conn, query);
		}else{
			query = "insert into WaitingToFollowList (listID, userFL) values (" + lid + ", '" + user + "')";
			executeUpdate(conn, query);
		}
	}

	public static void followListAccepted(Connection conn, String user, int lid) throws SQLException {
		// ToDo
		// insert into FollowsList values (lid, user);
		String query = "insert into FollowsList (listID, userFL) values (" + lid + ", '" + user + "')";
		executeUpdate(conn, query);
		query = "delete from WAitingToFollowList where listID = " + lid + "";
		executeUpdate(conn, query);

	}
	
	public static void unfollowList(Connection conn, String user, String name) throws SQLException {
		// ToDo
		// delete from FollowsList where listID = 'lid' and user = 'user';
		String query = "Select listID From Lists where name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		int lid = rs.getInt("listID");
		st.close();
		rs.close();
		query = "delete from FollowsList where listID = " + lid + " AND userFL='" + user + "'";
		executeUpdate(conn, query);
	}
	
	public static void unfollowList(Connection conn, String user, int lid) throws SQLException {
		// ToDo
		// delete from FollowsList where listID = 'lid' and user = 'user';
		String query = "delete from FollowsList where listID = " + lid + " AND userFL='" + user + "'";
		executeUpdate(conn, query);
	}
	
	public static void remWaitingList(Connection conn, String user, String name) throws SQLException {
		// ToDo
		// delete from ListHasWaitingMembers where member = 'user' and listID = 'lid';
		String query = "Select listID From Lists where name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		rs.next();
		int lid = rs.getInt("listID");
		st.close();
		rs.close();
		query = "delete from ListHasWaitingMembers where member='" + user + "' AND listID = " + lid + "";
		executeUpdate(conn, query);
	}
	
	public static LinkedList<String> getFollows(Connection conn, String user) throws SQLException {
		// ToDo
		// Get users you are following and users of people from lists you are following
		// temp = select followee from Follows where follower = 'user'
		// temp2 = select listID from FollowsList where user = 'user'
		// temp3 = select member from ListHasMembers where listID = temp2;
		String query = "select followee from Follows where follower='"  + user + "' UNION" 
			+ "select member from ListHasMembers where listID in (select listID from FollowsList where user = '" 
			+ user + "')";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString(1));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	// return author, content, date, msgID 
	public static LinkedList<LinkedList<String>> getAllTweets(Connection conn, String user) throws SQLException {
		// ToDo	
		
		/*String query = "SELECT * FROM (SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender"
			+ "FROM Writes W, Msgs T, Tweets U WHERE (W.author=`" + user + "` AND W.msgID ="
			+ "T.msgID AND T.msgID = U.msgID) UNION SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content,"
			+ "W.author AS sender FROM Follows F, Writes W, Msgs T, Tweets U WHERE (F.follower='"
			+ user + "' AND W.author = F.followee AND T.msgID = W.msgID AND T.msgID = U.msgID) UNION SELECT T.msgID AS msgID,"
			+ "T.msgDate AS msgDate, T.content AS content, W.author AS sender FROM FollowsList LF,"
			+ "ListHasMembers LM, Writes W, Msgs T, Tweets U WHERE (LF.userFL=`"
			+ user + "` AND LM.listID = LF.listID AND W.author = LM.member AND T.msgID = W.msgID AND T.msgID = U.msgID) UNION"
			+ "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender"
			+ "FROM Mentions M, Msgs T, Tweets U WHERE (M.name=`" + user + "` AND T.msgID ="
			+ "M.msgID AND W.msgID = T.msgID AND T.msgID = U.msgID))ORDER BY msgDate DESC";*/
		// The better version
		String query = "SELECT *"
				+ "FROM ("
			    + "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
			    + "FROM Writes W, Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
			    + "WHERE ("
			    + "W.author = '" + user + "' AND "
			    + "W.msgID = T.msgID) "
			    + "UNION "
			    + "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
			    + "FROM Follows F, Writes W,  Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
			    + "WHERE ("
			    + "    F.follower = '" + user + "' AND "
			    + "    W.author = F.followee AND "
			    + "   T.msgID = W.msgID) "
			    + "UNION "
			    + "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
			    + "FROM FollowsList LF, ListHasMembers LM, Writes W,  Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
			    + "WHERE ("
			    + "    LF.userFL = '" + user + "' AND "
			    + "    LM.listID = LF.listID AND "
			    + "    W.author = LM.member AND "
			    + "    T.msgID = W.msgID) "
			    + "UNION "
			    + "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
			    + "FROM Mentions M, Writes W, Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
			    + "WHERE ("
			    + "    M.name = '" + user + "' AND "
			    + "    T.msgID = M.msgID AND "
			    + "    W.msgID = T.msgID) "
			    + ") "
			    + "ORDER BY msgDate DESC";
		
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<String> getWaitingToFollow(Connection conn, String user) throws SQLException{
		String query = "select follower from WaitingToFollow where followee = '" + user + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString(1));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<LinkedList<String>> getMyTweets(Connection conn, String user) throws SQLException {
		// ToDo
		// Gets tweets of the user
		// select m.msgID, m.content, m.msgDate from Writes w, Tweets t, Msgs m 
		// where w.author = 'user' and w.msgID = t.msgID and t.msgID = m.msgID
		/*String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content W.author AS sender FROM Writes W,"
			+ "Msgs T, Tweets U WHERE (W.author=`" + user + "` AND W.msgID = T.msgID AND T.msgID = U.msgID) ORDER BY msgDate DESC";*/
		String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
						+ "FROM Writes W, Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
						+ "WHERE ("
						+ "W.author = '" + user + "' AND "
						+ "W.msgID = T.msgID "
						+ ") "
						+ "ORDER BY msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<LinkedList<String>> getMyFolloweesTweets(Connection conn, String user) throws SQLException {
		// ToDo
		// Gets tweets of users that the user is following
		// select followee from Follows where follower = 'user'; 
		// select m.msgID, m.content, m.msgDate from Writes w, Tweets t, Msgs m 
		// where w.author = 'user' and w.msgID = t.msgID and t.msgID = m.msgID
		/*String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS"
			+ "sender FROM Follows F, Writes W, Msgs T, Tweets U WHERE (F.follower=`"
			+ user + "` AND W.author = F.followee AND T.msgID = W.msgID AND T.msgID = U.msgID) ORDER BY msgDate DESC";
		*/
		String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
						+ "FROM Follows F, Writes W,  Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
						+ "WHERE ("
						+ "F.follower = '" + user + "' AND "
						+ "W.author = F.followee AND "
						+ "T.msgID = W.msgID) "
						+ "ORDER BY msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<LinkedList<String>> getFollowedListTweets(Connection conn, String user, int lid) throws SQLException {
		// ToDo
		// Gets tweets of a list of users that you are following
		/*String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS"
			+ "sender FROM FollowsList LF, ListHasMembers LM, Writes W, Msgs T, Tweets U WHERE"
			+ "(LF.userFL=`" + user + "` LF.listID = '" + lid + "' AND LM.listID = LF.listID AND W.author ="
			+ "LM.member AND T.msgID = W.msgID AND U.msgID = T.msgID) ORDER BY msgDate DESC";*/
		String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
						+ "FROM ListHasMembers LM, Writes W, Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
						+ "WHERE ("
						+ "LM.listID = " + lid + " AND "
						+ "W.author = LM.member AND "
						+ "T.msgID = W.msgID "
						+ ") "
						+ "ORDER BY T.msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}		
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<String> getAllFollowedLists(Connection conn, String user) throws SQLException {
		// ToDo
		// Gets tweets of users from all lists that you are following
		/*String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS"
			+ "sender FROM FollowsList LF, ListHasMembers LM, Writes W, Msgs T, Tweets U WHERE"
			+ "(LF.userFL=`" + user + "` AND LM.listID = LF.listID AND W.author = LM.member AND T.msgID ="
			+ "W.msgID AND U.msgID = T.msgID) ORDER BY msgDate DESC";*/
		String query = "SELECT l.name AS name FROM FollowsList f, Lists l WHERE f.userFL = '" 
			+ user + "' AND l.listID = f.listID";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString("name"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<LinkedList<String>> getAllFollowedListTweets(Connection conn, String user) throws SQLException {
		// ToDo
		// Gets tweets of users from all lists that you are following
		/*String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS"
			+ "sender FROM FollowsList LF, ListHasMembers LM, Writes W, Msgs T, Tweets U WHERE"
			+ "(LF.userFL=`" + user + "` AND LM.listID = LF.listID AND W.author = LM.member AND T.msgID ="
			+ "W.msgID AND U.msgID = T.msgID) ORDER BY msgDate DESC";*/
		String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
						+ "FROM FollowsList LF, ListHasMembers LM, Writes W,  Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
						+ "WHERE ("
						+ "LF.userFL = '" + user + "' AND "
						+ "LM.listID = LF.listID AND "
						+ "W.author = LM.member AND "
						+ "T.msgID = W.msgID) "
						+ "ORDER BY T.msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<String> getFollowers(Connection conn, String user) throws SQLException{
		String query = "select follower from Follows where followee = '" + user + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		if(rs != null){
			while(rs.next()){
				ret.add(rs.getString(1));
			}
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<String> getFollowees(Connection conn, String user) throws SQLException{
		String query = "select followee from Follows where follower = '" + user + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString(1));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static String getMsgString(Connection conn, int mid) throws SQLException{
		String query = "select content from Msgs where msgID = " + mid + "";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		String ret = "";
		while(rs.next()){
			ret = rs.getString(1);
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<LinkedList<String>> getPrivateMessages(Connection conn, String user) throws SQLException {
		String query = "SELECT M.msgID AS msgID, M.msgDate AS msgDate, M.content AS content, W.author AS sender, PM.addressee AS recipient "
						+ "FROM Msgs M INNER JOIN PrivateMsgs PM ON PM.msgID = M.msgID, Writes W "
						+ "WHERE ("
						+ "(PM.addressee = '" + user + "' OR W.author = '" + user + "') AND "
						+ "W.msgID = M.msgID "
						+ ") "
						+ "ORDER BY M.msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 5; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
			ret.get(4).add(rs.getString("recipient"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<String> getWhoToFolSuggestions(Connection conn, String user) throws SQLException {
		// ToDo
		// Suggest random people that the user might want to follow
		// query FOLLOWS for a count of how many of the people you follow follow everyone else and then
		// sort by count and truncate ??????????????????
		//String query = "select f.followee from Follows f, Follows g where g.follower='" + user + "g.followee = f.follower AND count(f.followee) > 1 Order by count(f.followee) DESC";
		String query = "Select * From ("
			+ "SELECT u.name AS name FROM Users u WHERE u.name != '" 
			+ user + "' MINUS Select x.name AS name from Follows f, Users x WHERE f.follower = '" 
			+ user + "' AND f.followee = x.name) ORDER BY DBMS_RANDOM.value";
		/*query = "Select * From ("
			+ "Select ";*/
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString(1));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<LinkedList<String>> getMentions(Connection conn, String user, String name) throws SQLException {
		// ToDo
		// Get all the tweets of the specified mention
		//Union with a query on the PM's hedged for privacy before the order by statement
		/*String query = "select * from (SELECT m.msgID AS msgID, m.msgDate AS msgDate, m.content AS content, "
			+ "w.author AS sender from (Tweets NATURAL JOIN Msgs) m, Mentions h, Writes w"
			+ "where h.name = '" + name + "' AND m.msgID = h.msgID  AND w.msgID = m.msgID UNION SELECT"
			+ "p.msgID AS msgID, p.msgDate AS msgDate, p.content AS content, x.author AS sender"
			+ "from (PrivateMsgs NATURAL JOIN Msgs) p, Mentions i, Writes x where i.name = '" 
			+ name + "' AND p.msgID = i.msgID AND x.msgID = p.msgID AND (p.addressee = '" + user 
			+ "' OR x.author = '" + user + "')order by msgDate DESC";*/
		String query = "select * from ("
						+ "SELECT m.msgID AS msgID, m.msgDate AS msgDate, m.content AS content, w.author AS sender "
						+ "FROM Tweets T INNER JOIN Msgs m ON T.msgID = m.msgID, Mentions h, Writes w where h.name = '" + name + "' AND m.msgID = h.msgID  AND w.msgID = m.msgID "
						+ "UNION "
						+ "SELECT p.msgID AS msgID, p.msgDate AS msgDate, p.content AS content, x.author AS sender "
						+ "FROM PrivateMsgs Pr INNER JOIN Msgs p ON Pr.msgID=p.msgID, Mentions i, Writes x "
						+ "WHERE i.name = '" + name + "' AND p.msgID = i.msgID AND x.msgID = p.msgID AND (Pr.addressee = '" + user + "' OR x.author = '" + user + "')) "
						+ "order by msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}
		st.close();
		rs.close();
		return ret;
		
	}
	
	public static LinkedList<LinkedList<String>> getHashtags(Connection conn, String user, String ht) throws SQLException {
		// ToDo
		// Get all the tweets of the specified hashtag
		//Union with a query on the PM's hedged for privacy before the order by statement
		/*String query = "select * from (SELECT m.msgID AS msgID, m.msgDate AS msgDate, m.content AS content, "
			+ "w.author AS sender from (Tweets NATURAL JOIN Msgs) m, ContainsHashTags h,"
			+ "Writes w where h.tag = '" + ht + "' AND m.msgID = h.msgID AND w.msgID = m.msgID UNION SELECT"
			+ "p.msgID AS msgID, p.msgDate AS msgDate, p.content AS content, x.author AS sender"
			+ "from (PrivateMsgs NATURAL JOIN Msgs) p, ContainsHashTags i, Writes x where i.tag = '" 
			+ ht + "' AND p.msgID = i.msgID AND x.msgID = p.msgID AND (p.addressee = '" + user 
			+ "' OR x.author = '" + user + "') order by msgDate DESC";*/
		String query = "select * from ("
			+ "SELECT m.msgID AS msgID, m.msgDate AS msgDate, m.content AS content, w.author AS sender "
			+ "FROM Tweets T INNER JOIN Msgs m ON T.msgID = m.msgID, ContainsHashTags h, Writes w where h.tag = '" + ht + "' AND m.msgID = h.msgID  AND w.msgID = m.msgID "
			+ "UNION "
			+ "SELECT p.msgID AS msgID, p.msgDate AS msgDate, p.content AS content, x.author AS sender "
			+ "FROM PrivateMsgs Pr INNER JOIN Msgs p ON Pr.msgID=p.msgID, ContainsHashTags i, Writes x "
			+ "WHERE i.tag = '" + ht + "' AND p.msgID = i.msgID AND x.msgID = p.msgID AND (Pr.addressee = '" + user + "' OR x.author = '" + user + "')) "
			+ "order by msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	// All user's created lists + list followers + people waiting to follow
	public static LinkedList<LinkedList<String>> getUsersListInfo(Connection conn, String user) throws SQLException{
		/*String query = "select l.listID AS listID, l.name AS listName w.member AS waitingToFolow, f.userFL"
			+"AS follower from ListHasWaitingMembers w, FollowsList f, Lists l where l.creator = '"
			+ user + "' AND (l.listID = w.listID OR l.listID = f.listID) ORDER BY listID DESC";*/
		String query = "select l.listID AS listID, l.name AS listName, w.member AS waitingToFolow, f.userFL AS follower "
						+ "from ListHasWaitingMembers w, FollowsList f, Lists l "
						+ "where l.creator = '" + user + "' AND (l.listID = w.listID OR l.listID = f.listID) ORDER BY listID DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("listID"));
			ret.get(1).add(rs.getString("listName"));
			ret.get(2).add(rs.getString("waitingToFollow"));
			ret.get(3).add(rs.getString("follower"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static void addFavorite(Connection conn, String user, int MID) throws SQLException{

		String query = "insert into Favorites (msgID, favoriter) values (" + MID + ", '" + user + "')";
		executeUpdate(conn, query);
	}
	
	public static void remFavorite(Connection conn, String user, int mid) throws SQLException {
		// ToDo
		// delete from ListHasWaitingMembers where member = 'user' and listID = 'lid';
		String query = "delete from Favorites where msgID = " + mid + " AND favoriter = '" + user + "'";
		executeUpdate(conn, query);
	}
	
	public static LinkedList<LinkedList<String>> getMyFavorites(Connection conn, String user) throws SQLException {
		// ToDo
		// Gets tweets of the user
		// select m.msgID, m.content, m.msgDate from Writes w, Tweets t, Msgs m 
		// where w.author = 'user' and w.msgID = t.msgID and t.msgID = m.msgID
		/*String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content W.author AS sender FROM Writes W,"
			+ "Msgs T, Tweets U WHERE (W.author=`" + user + "` AND W.msgID = T.msgID AND T.msgID = U.msgID) ORDER BY msgDate DESC";*/
		String query = "SELECT T.msgID AS msgID, T.msgDate AS msgDate, T.content AS content, W.author AS sender "
						+ "FROM Writes W, Favorites f, Msgs T INNER JOIN Tweets U ON T.msgID = U.msgID "
						+ "WHERE ("
						+ "f.favoriter = '" + user + "' AND "
						+ "f.msgID = T.msgID AND "
						+ "W.msgID = f.msgID "
						+ ") "
						+ "ORDER BY msgDate DESC";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 4; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("sender"));
			ret.get(1).add(rs.getString("content"));
			ret.get(2).add("" + rs.getTimestamp("msgDate"));
			ret.get(3).add("" + rs.getInt("msgID"));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static void updateInfoList(Connection conn, String name, String[] args) throws SQLException{
		String[] fields = {"isPrivate", "description"};
		String qargs = "update Lists set ";
		for(int i = 0; i < args.length; i++){
			qargs += fields[i] +  " = '" + args[i] + "'";
			if (i == args.length - 1)
				break;
			qargs += ", ";
		}
		qargs += " where name = '" + name + "'";
		executeUpdate(conn, qargs);
	}
	
	public static void updateInfoList(Connection conn, int lid, String[] args) throws SQLException{
		String[] fields = {"isPrivate", "description"};
		String qargs = "update Lists set ";
		for(int i = 0; i < args.length; i++){
			qargs += fields[i] +  " = '" + args[i] + "'";
			if (i == args.length - 1)
				break;
			qargs += ", ";
		}
		qargs += " where listID = " + lid + "";
		executeUpdate(conn, qargs);
	}
	
	public static LinkedList<String> getUserLists(Connection conn, String user) throws SQLException{
		//returns names of lists made by user
		String query = "Select name From Lists where creator = '" + user + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString(1));
		}
		st.close();
		rs.close();
		return ret;
	}

	public static LinkedList<LinkedList<String>> getListInfo(Connection conn, String name) throws SQLException{
		/*LinkedList[0] = listName (need to return the name even though given as argument)
		LinkedList[1] = listDesc
		LinkedList[2] = listPrivacy
		LinkedList[3] = author
		LinkedList[4] = LinkedList(members)
		LinkedList[5] = LinkedList(members waiting to follow)*/
		String query = "SELECT * "
			+ "FROM Lists "
			+ "WHERE "
			+ "name = '" + name + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		
		int lid = 1;
		
		LinkedList<LinkedList<String>> ret = new LinkedList<LinkedList<String>>();
		for(int i = 0; i < 9; i++){
			ret.add(new LinkedList<String>());
		}
		while(rs.next()){
			ret.get(0).add(rs.getString("name"));
			ret.get(1).add(rs.getString("description"));
			ret.get(2).add(rs.getString("isPrivate"));
			ret.get(3).add(rs.getString("creator"));
			lid = rs.getInt("listID");
			ret.get(6).add("" + lid);
		}
		st.close();
		rs.close();
		
		//System.out.println(lid);
		
		query = "Select member From ListHasMembers Where listID = " + lid + "";
		
		st = conn.createStatement();
		rs = st.executeQuery(query);
		
		while(rs.next()){
			//System.out.println(lid + " " + rs.getString("member"));
			ret.get(4).add(rs.getString("member"));
		}
		st.close();
		rs.close();
		
		query = "Select member From ListHasWaitingMembers Where listID = " + lid + "";
		
		st = conn.createStatement();
		rs = st.executeQuery(query);
		
		while(rs.next()){
			ret.get(5).add(rs.getString("member"));
		}
		st.close();
		rs.close();
		
		query = "Select userFL From WaitingToFollowList Where listID = " + lid + "";
		
		st = conn.createStatement();
		rs = st.executeQuery(query);
		
		while(rs.next()){
			ret.get(7).add(rs.getString("userFL"));
		}
		st.close();
		rs.close();
		
		query = "Select userFL From FollowsList Where listID = " + lid + "";
		
		st = conn.createStatement();
		rs = st.executeQuery(query);
		
		while(rs.next()){
			ret.get(8).add(rs.getString("userFL"));
		}
		st.close();
		rs.close();
		
		return ret;
	}

	public static LinkedList<String> getSearchLists(Connection conn, String searchTerm) throws SQLException{
		//returns linked list of names of lists where the term is found in a list description or list name
		String query = "Select name From Lists Where description LIKE '%" 
			+ searchTerm + "%' OR name LIKE '%" + searchTerm + "%'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString(1));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static LinkedList<String> getSearchUsers(Connection conn, String searchTerm) throws SQLException{
		//returns linked list of names of lists where the term is found in a list description or list name
		String query = "Select name From Users Where firstName LIKE '%" 
			+ searchTerm + "%' OR lastName LIKE '%" 
			+ searchTerm + "%' OR bio LIKE '%" 
			+ searchTerm + "%' OR name LIKE '%" + searchTerm + "%'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		LinkedList<String> ret = new LinkedList<String>();
		while(rs.next()){
			ret.add(rs.getString(1));
		}
		st.close();
		rs.close();
		return ret;
	}
	
	public static void main (String args[]) {

		if (args.length < 4) {
			System.out.println("Not enough arguments: Registrar dbUser dbPass dbSID dbHost");
			System.exit(0);
		}
		String dbUser = args[0].trim();
		String dbPass = args[1].trim();
		String dbSID = args[2].trim();
		String dbHost = args[3].trim();
		int dbPort = 1521;

		Connection _conn = null;

		try {
			_conn = openDBConnection(dbUser, dbPass, dbSID, dbHost, dbPort);
			//System.out.println(getUser(_conn, "stu", "art"));

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		finally {
			try {
				_conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}





